package control;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;

import model.ScrollPic;
import util.Pagination;
import dbconn.DBConnect;

public class ScrollPicControl {
    public static Pagination pageInstance = null;
	
	public static boolean AddScrollPic(List<ScrollPic> delist) throws ParseException{
	    DBConnect dbc = new DBConnect();
	    String sql = "insert into scrollpic(pic,description)values(?,?)";
	    PreparedStatement pstmt = null;
	    Iterator<ScrollPic> iterator = delist.iterator();
	    dbc.setAutoCommit(false);
		dbc.prepareStatement(sql);
		pstmt = dbc.getPreparedStatement();
	    try{
		     while(iterator.hasNext()){
			 ScrollPic pn = (ScrollPic)iterator.next();
    	     int index = 1; 
	             pstmt.setString(index++, pn.getPic());
	             pstmt.setString(index++, pn.getDescription());
	             pstmt.addBatch();
		         pstmt.executeBatch();
	             dbc.commit();
		     }
	     }catch(SQLException e){
	        e.printStackTrace();
		   System.out.println("批量添加滚动图片失败！\n");
		   return false;
	   }finally{
		dbc.close();
	}
	return true;
}
	
	public static ScrollPic getScrollPic(int id){
		DBConnect dbc=new DBConnect();
		String sql = "select * from scrollpic where id='"+id+"'";
		dbc.prepareStatement(sql);
		try {
			ResultSet rs= dbc.executeQuery();
			if(rs.next()){
			        ScrollPic pn = new ScrollPic();
				pn.setId(rs.getInt("id"));
				pn.setDescription(rs.getString("description"));
				pn.setPic(rs.getString("pic"));
				return pn;
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}finally{
			dbc.close();
		}
		return null;
	}
	
	
	public static boolean updateScrollPic(ScrollPic pn,int id){
		DBConnect dbConnect = new DBConnect();
		String sql = "update scrollpic set description=?,pic=? where id=?";
		dbConnect.prepareStatement(sql);
		try {
			int index = 1;
			dbConnect.setString(index++, pn.getDescription());
			dbConnect.setString(index++, pn.getPic());
			dbConnect.setInt(index++, id);
			dbConnect.executeUpdate();
			return true;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return false;
		}finally{
			dbConnect.close();
		}
	}
	
	
	public static boolean deleteScrollPic(int id){
		DBConnect dbConnect = new DBConnect();
		String sql = "delete from scrollpic where id='"+id+"'";
		dbConnect.prepareStatement(sql);
		try {
			dbConnect.executeUpdate();
			return true;
		} catch (Exception e) {
			// TODO: handle exception
		    e.printStackTrace();
		    return false;
		}finally{
			dbConnect.close();
		}
	}
	
	/*public List<PicNews> getAllPicNews() {
		List<PicNews> list = new ArrayList<PicNews>();
		String sql ="select * from picnews";
		DBConnect dbConnect=  new DBConnect();
		ResultSet rsResultSet = null;
		dbConnect.prepareStatement(sql);
		try {
			rsResultSet = dbConnect.executeQuery();
			while(rsResultSet.next()){
				PicNews pn = new PicNews();
				pn.setId(rsResultSet.getInt("id"));
				pn.setAddtime(rsResultSet.getString("addtime"));
				pn.setContent(rsResultSet.getString("content"));
				pn.setPic(rsResultSet.getString("pic"));
				list.add(pn);
			}
			return list;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}finally{
			dbConnect.close();
		}
	}
	*/
	
	//获取指定数量的滚动图片
	public static List<ScrollPic> getSomeScrollPics(int num) {
	    List<ScrollPic> news = new ArrayList<ScrollPic>();
	    DBConnect dbc = new DBConnect();
	    String sql = "select * from scrollpic order by id desc  limit "+num+"";
	    dbc.prepareStatement(sql);
	    System.out.println(sql);
	    try {
		ResultSet rsResultSet = dbc.executeQuery();
		while(rsResultSet.next()){
		        ScrollPic pn = new ScrollPic();
			pn.setId(rsResultSet.getInt("id"));
			pn.setDescription(rsResultSet.getString("description"));
			pn.setPic(rsResultSet.getString("pic"));
			news.add(pn);
		}
		return news;
	    } catch (Exception e) {
		// TODO: handle exception
		e.printStackTrace();
		return news;
	    }finally{
		dbc.close();
	    }
	}
	
	//分页显示
	public  Collection GetAllScrollPic(int page,int rowsPerPage) throws SQLException, Exception
	{
		String sql = "SELECT * FROM scrollpic order by id desc";
		System.out.println(sql);
		pageInstance = new PicNewsControlPage(sql, rowsPerPage);
		return pageInstance.getPage(page);
	}

	class PicNewsControlPage extends Pagination {
	public PicNewsControlPage(String sql, int rowsPerPage) {
		super(sql, rowsPerPage, 1);
	}
	protected Collection<ScrollPic> packResultSet(ResultSet rsResultSet) throws SQLException {
		Collection<ScrollPic> PicNewsList = new ArrayList<ScrollPic>();
		while (rsResultSet.next()) {
		        ScrollPic pn = new ScrollPic();
			pn.setId(rsResultSet.getInt("id"));
			pn.setDescription(rsResultSet.getString("description"));
			pn.setPic(rsResultSet.getString("pic"));
			PicNewsList.add(pn);
		}
		return PicNewsList;
	}
}

}
